

--  Contact

-- Get
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_Get') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_Get
GO

CREATE PROCEDURE dbo.uspContact_Get
(
	@Where varchar (2000)  ,
	@OrderBy varchar (2000)  ,
	@Ascending bit ,
	@StartRowIndex int   ,
	@MaximumRows int   
)

AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @StartRowIndex
SET @PageUpperBound = @PageLowerBound + @MaximumRows

IF (@OrderBy is null or LEN(@OrderBy) < 1)
BEGIN
	-- default order by to first column
	SET @OrderBy = '[ContactID]'
END

-- SQL Server 2005 Paging
declare @SQL as nvarchar(4000)
SET @SQL = 'WITH PageIndex AS ('
SET @SQL = @SQL + ' SELECT'
IF @MaximumRows > 0
BEGIN
	SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
SET @SQL = @SQL + ', [ContactID]'
SET @SQL = @SQL + ', [Name]'
SET @SQL = @SQL + ', [Address]'
SET @SQL = @SQL + ', [Version]'
SET @SQL = @SQL + ' FROM dbo.[Contact]'
IF LEN(@Where) > 0
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @Where
END
SET @SQL = @SQL + ' ) SELECT'
SET @SQL = @SQL + ' [ContactID], [Name], [Address], [Version]'
SET @SQL = @SQL + ' FROM PageIndex'
SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
IF @MaximumRows > 0
BEGIN
	SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy

IF @Ascending = 0
    SET @SQL = @SQL + ' DESC'

exec sp_executesql @SQL

END
GO




-- GetFirst
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_GetFirst') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_GetFirst
GO

CREATE PROCEDURE dbo.uspContact_GetFirst
(
	@Where varchar(2000),
    @OrderBy varchar(2000),
    @Ascending bit
)

AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT TOP 1 ContactID, Name, Address, Version'
SET @SQL = @SQL + ' FROM dbo.[Contact]'

IF LEN(@Where) > 0
	SET @SQL = @SQL + ' WHERE ' + @Where

SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
IF @Ascending = 0
    SET @SQL = @SQL + ' DESC'

exec sp_executesql @SQL
GO



-- GetCount
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_GetCount') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_GetCount
GO

CREATE PROCEDURE dbo.uspContact_GetCount
(
	@Where varchar(2000)
)

AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT Count(*)'
SET @SQL = @SQL + ' FROM dbo.[Contact]'

IF LEN(@Where) > 0
	SET @SQL = @SQL + ' WHERE ' + @Where

exec sp_executesql @SQL
GO




--  Contact

-- Get
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_Get') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_Get
GO

CREATE PROCEDURE dbo.uspContact_Get
(
	@Where varchar (2000)  ,
	@OrderBy varchar (2000)  ,
	@Ascending bit ,
	@StartRowIndex int   ,
	@MaximumRows int   
)

AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @StartRowIndex
SET @PageUpperBound = @PageLowerBound + @MaximumRows

IF (@OrderBy is null or LEN(@OrderBy) < 1)
BEGIN
	-- default order by to first column
	SET @OrderBy = '[ContactID]'
END

-- SQL Server 2005 Paging
declare @SQL as nvarchar(4000)
SET @SQL = 'WITH PageIndex AS ('
SET @SQL = @SQL + ' SELECT'
IF @MaximumRows > 0
BEGIN
	SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
SET @SQL = @SQL + ', [ContactID]'
SET @SQL = @SQL + ', [Name]'
SET @SQL = @SQL + ', [Address]'
SET @SQL = @SQL + ', [Version]'
SET @SQL = @SQL + ' FROM dbo.[Contact]'
IF LEN(@Where) > 0
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @Where
END
SET @SQL = @SQL + ' ) SELECT'
SET @SQL = @SQL + ' [ContactID], [Name], [Address], [Version]'
SET @SQL = @SQL + ' FROM PageIndex'
SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
IF @MaximumRows > 0
BEGIN
	SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy

IF @Ascending = 0
    SET @SQL = @SQL + ' DESC'

exec sp_executesql @SQL

END
GO




-- GetFirst
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_GetFirst') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_GetFirst
GO

CREATE PROCEDURE dbo.uspContact_GetFirst
(
	@Where varchar(2000),
    @OrderBy varchar(2000),
    @Ascending bit
)

AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT TOP 1 ContactID, Name, Address, Version'
SET @SQL = @SQL + ' FROM dbo.[Contact]'

IF LEN(@Where) > 0
	SET @SQL = @SQL + ' WHERE ' + @Where

SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
IF @Ascending = 0
    SET @SQL = @SQL + ' DESC'

exec sp_executesql @SQL
GO



-- GetCount
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_GetCount') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_GetCount
GO

CREATE PROCEDURE dbo.uspContact_GetCount
(
	@Where varchar(2000)
)

AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT Count(*)'
SET @SQL = @SQL + ' FROM dbo.[Contact]'

IF LEN(@Where) > 0
	SET @SQL = @SQL + ' WHERE ' + @Where

exec sp_executesql @SQL
GO




--  Member

-- Get
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMember_Get') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMember_Get
GO

CREATE PROCEDURE dbo.uspMember_Get
(
	@Where varchar (2000)  ,
	@OrderBy varchar (2000)  ,
	@Ascending bit ,
	@StartRowIndex int   ,
	@MaximumRows int   
)

AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @StartRowIndex
SET @PageUpperBound = @PageLowerBound + @MaximumRows

IF (@OrderBy is null or LEN(@OrderBy) < 1)
BEGIN
	-- default order by to first column
	SET @OrderBy = '[MemberID]'
END

-- SQL Server 2005 Paging
declare @SQL as nvarchar(4000)
SET @SQL = 'WITH PageIndex AS ('
SET @SQL = @SQL + ' SELECT'
IF @MaximumRows > 0
BEGIN
	SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
SET @SQL = @SQL + ', [MemberID]'
SET @SQL = @SQL + ', [Name]'
SET @SQL = @SQL + ' FROM dbo.[Member]'
IF LEN(@Where) > 0
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @Where
END
SET @SQL = @SQL + ' ) SELECT'
SET @SQL = @SQL + ' [MemberID], [Name]'
SET @SQL = @SQL + ' FROM PageIndex'
SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
IF @MaximumRows > 0
BEGIN
	SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy

IF @Ascending = 0
    SET @SQL = @SQL + ' DESC'

exec sp_executesql @SQL

END
GO




-- GetFirst
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMember_GetFirst') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMember_GetFirst
GO

CREATE PROCEDURE dbo.uspMember_GetFirst
(
	@Where varchar(2000),
    @OrderBy varchar(2000),
    @Ascending bit
)

AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT TOP 1 MemberID, Name'
SET @SQL = @SQL + ' FROM dbo.[Member]'

IF LEN(@Where) > 0
	SET @SQL = @SQL + ' WHERE ' + @Where

SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
IF @Ascending = 0
    SET @SQL = @SQL + ' DESC'

exec sp_executesql @SQL
GO



-- GetCount
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMember_GetCount') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMember_GetCount
GO

CREATE PROCEDURE dbo.uspMember_GetCount
(
	@Where varchar(2000)
)

AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT Count(*)'
SET @SQL = @SQL + ' FROM dbo.[Member]'

IF LEN(@Where) > 0
	SET @SQL = @SQL + ' WHERE ' + @Where

exec sp_executesql @SQL
GO




--  Memo

-- Get
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMemo_Get') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMemo_Get
GO

CREATE PROCEDURE dbo.uspMemo_Get
(
	@Where varchar (2000)  ,
	@OrderBy varchar (2000)  ,
	@Ascending bit ,
	@StartRowIndex int   ,
	@MaximumRows int   
)

AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @StartRowIndex
SET @PageUpperBound = @PageLowerBound + @MaximumRows

IF (@OrderBy is null or LEN(@OrderBy) < 1)
BEGIN
	-- default order by to first column
	SET @OrderBy = '[MemoID]'
END

-- SQL Server 2005 Paging
declare @SQL as nvarchar(4000)
SET @SQL = 'WITH PageIndex AS ('
SET @SQL = @SQL + ' SELECT'
IF @MaximumRows > 0
BEGIN
	SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
SET @SQL = @SQL + ', [MemoID]'
SET @SQL = @SQL + ', [WrittenAt]'
SET @SQL = @SQL + ', [Subject]'
SET @SQL = @SQL + ', [Body]'
SET @SQL = @SQL + ', [ReadCount]'
SET @SQL = @SQL + ', [ParentID]'
SET @SQL = @SQL + ' FROM dbo.[Memo]'
IF LEN(@Where) > 0
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @Where
END
SET @SQL = @SQL + ' ) SELECT'
SET @SQL = @SQL + ' [MemoID], [WrittenAt], [Subject], [Body], [ReadCount], [ParentID]'
SET @SQL = @SQL + ' FROM PageIndex'
SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
IF @MaximumRows > 0
BEGIN
	SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy

IF @Ascending = 0
    SET @SQL = @SQL + ' DESC'

exec sp_executesql @SQL

END
GO




-- GetFirst
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMemo_GetFirst') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMemo_GetFirst
GO

CREATE PROCEDURE dbo.uspMemo_GetFirst
(
	@Where varchar(2000),
    @OrderBy varchar(2000),
    @Ascending bit
)

AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT TOP 1 MemoID, WrittenAt, Subject, Body, ReadCount, ParentID'
SET @SQL = @SQL + ' FROM dbo.[Memo]'

IF LEN(@Where) > 0
	SET @SQL = @SQL + ' WHERE ' + @Where

SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
IF @Ascending = 0
    SET @SQL = @SQL + ' DESC'

exec sp_executesql @SQL
GO



-- GetCount
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMemo_GetCount') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMemo_GetCount
GO

CREATE PROCEDURE dbo.uspMemo_GetCount
(
	@Where varchar(2000)
)

AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT Count(*)'
SET @SQL = @SQL + ' FROM dbo.[Memo]'

IF LEN(@Where) > 0
	SET @SQL = @SQL + ' WHERE ' + @Where

exec sp_executesql @SQL
GO




--  Reading

-- Get
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspReading_Get') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspReading_Get
GO

CREATE PROCEDURE dbo.uspReading_Get
(
	@Where varchar (2000)  ,
	@OrderBy varchar (2000)  ,
	@Ascending bit ,
	@StartRowIndex int   ,
	@MaximumRows int   
)

AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @StartRowIndex
SET @PageUpperBound = @PageLowerBound + @MaximumRows

IF (@OrderBy is null or LEN(@OrderBy) < 1)
BEGIN
	-- default order by to first column
	SET @OrderBy = '[MemberID]'
END

-- SQL Server 2005 Paging
declare @SQL as nvarchar(4000)
SET @SQL = 'WITH PageIndex AS ('
SET @SQL = @SQL + ' SELECT'
IF @MaximumRows > 0
BEGIN
	SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
SET @SQL = @SQL + ', [MemberID]'
SET @SQL = @SQL + ', [MemoID]'
SET @SQL = @SQL + ' FROM dbo.[Reading]'
IF LEN(@Where) > 0
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @Where
END
SET @SQL = @SQL + ' ) SELECT'
SET @SQL = @SQL + ' [MemberID], [MemoID]'
SET @SQL = @SQL + ' FROM PageIndex'
SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
IF @MaximumRows > 0
BEGIN
	SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy

IF @Ascending = 0
    SET @SQL = @SQL + ' DESC'

exec sp_executesql @SQL

END
GO




-- GetFirst
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspReading_GetFirst') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspReading_GetFirst
GO

CREATE PROCEDURE dbo.uspReading_GetFirst
(
	@Where varchar(2000),
    @OrderBy varchar(2000),
    @Ascending bit
)

AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT TOP 1 MemberID, MemoID'
SET @SQL = @SQL + ' FROM dbo.[Reading]'

IF LEN(@Where) > 0
	SET @SQL = @SQL + ' WHERE ' + @Where

SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
IF @Ascending = 0
    SET @SQL = @SQL + ' DESC'

exec sp_executesql @SQL
GO



-- GetCount
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspReading_GetCount') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspReading_GetCount
GO

CREATE PROCEDURE dbo.uspReading_GetCount
(
	@Where varchar(2000)
)

AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT Count(*)'
SET @SQL = @SQL + ' FROM dbo.[Reading]'

IF LEN(@Where) > 0
	SET @SQL = @SQL + ' WHERE ' + @Where

exec sp_executesql @SQL
GO




--  ReadingView

-- Get
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspReadingView_Get') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspReadingView_Get
GO

CREATE PROCEDURE dbo.uspReadingView_Get
(
	@Where varchar (2000)  ,
	@OrderBy varchar (2000)  ,
	@Ascending bit ,
	@StartRowIndex int   ,
	@MaximumRows int   
)

AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @StartRowIndex
SET @PageUpperBound = @PageLowerBound + @MaximumRows

IF (@OrderBy is null or LEN(@OrderBy) < 1)
BEGIN
	-- default order by to first column
	SET @OrderBy = '[MemberName]'
END

-- SQL Server 2005 Paging
declare @SQL as nvarchar(4000)
SET @SQL = 'WITH PageIndex AS ('
SET @SQL = @SQL + ' SELECT'
IF @MaximumRows > 0
BEGIN
	SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
SET @SQL = @SQL + ', [MemberName]'
SET @SQL = @SQL + ', [Subject]'
SET @SQL = @SQL + ', [WrittenAt]'
SET @SQL = @SQL + ', [Body]'
SET @SQL = @SQL + ', [ReadCount]'
SET @SQL = @SQL + ' FROM dbo.[ReadingView]'
IF LEN(@Where) > 0
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @Where
END
SET @SQL = @SQL + ' ) SELECT'
SET @SQL = @SQL + ' [MemberName], [Subject], [WrittenAt], [Body], [ReadCount]'
SET @SQL = @SQL + ' FROM PageIndex'
SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
IF @MaximumRows > 0
BEGIN
	SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy

IF @Ascending = 0
    SET @SQL = @SQL + ' DESC'

exec sp_executesql @SQL

END
GO




-- GetFirst
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspReadingView_GetFirst') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspReadingView_GetFirst
GO

CREATE PROCEDURE dbo.uspReadingView_GetFirst
(
	@Where varchar(2000),
    @OrderBy varchar(2000),
    @Ascending bit
)

AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT TOP 1 MemberName, Subject, WrittenAt, Body, ReadCount'
SET @SQL = @SQL + ' FROM dbo.[ReadingView]'

IF LEN(@Where) > 0
	SET @SQL = @SQL + ' WHERE ' + @Where

SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
IF @Ascending = 0
    SET @SQL = @SQL + ' DESC'

exec sp_executesql @SQL
GO



-- GetCount
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspReadingView_GetCount') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspReadingView_GetCount
GO

CREATE PROCEDURE dbo.uspReadingView_GetCount
(
	@Where varchar(2000)
)

AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT Count(*)'
SET @SQL = @SQL + ' FROM dbo.[ReadingView]'

IF LEN(@Where) > 0
	SET @SQL = @SQL + ' WHERE ' + @Where

exec sp_executesql @SQL
GO






--  Contact

-- GetByPK
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_GetByContactID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_GetByContactID
GO

CREATE PROCEDURE [dbo].[uspContact_GetByContactID]
		@ContactID Int
AS

SET NOCOUNT ON

SELECT
		[ContactID],
	[Name],
	[Address],
	[Version]
FROM
	[dbo].[Contact]
WHERE
		[ContactID] = @ContactID

GO




-- Insert
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_Insert
GO

CREATE PROCEDURE [dbo].[uspContact_Insert]
		@Name NVarChar(50),
	@Address NVarChar(50)
	,
		@ContactID Int OUTPUT

AS

SET NOCOUNT ON

INSERT INTO [dbo].[Contact] (
		[Name],
	[Address]
) VALUES (
		@Name,
	@Address
)

SET @ContactID = SCOPE_IDENTITY()

GO




-- Update

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_Update
GO

CREATE PROCEDURE [dbo].[uspContact_Update]
		@ContactID Int,
	@Name NVarChar(50),
	@Address NVarChar(50)

AS

SET NOCOUNT ON

UPDATE [dbo].[Contact] SET
		[Name] = @Name,
	[Address] = @Address
WHERE
		[ContactID] = @ContactID

GO




-- Delete
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_Delete
GO

CREATE PROCEDURE [dbo].[uspContact_Delete]
		@ContactID Int

AS

SET NOCOUNT ON

DELETE FROM [dbo].[Contact]
WHERE
		[ContactID] = @ContactID

GO




--  Contact

-- GetByPK
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_GetByContactID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_GetByContactID
GO

CREATE PROCEDURE [dbo].[uspContact_GetByContactID]
		@ContactID Int
AS

SET NOCOUNT ON

SELECT
		[ContactID],
	[Name],
	[Address],
	[Version]
FROM
	[dbo].[Contact]
WHERE
		[ContactID] = @ContactID

GO




-- Insert
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_Insert
GO

CREATE PROCEDURE [dbo].[uspContact_Insert]
		@Name NVarChar(50),
	@Address NVarChar(50)
	,
		@ContactID Int OUTPUT

AS

SET NOCOUNT ON

INSERT INTO [dbo].[Contact] (
		[Name],
	[Address]
) VALUES (
		@Name,
	@Address
)

SET @ContactID = SCOPE_IDENTITY()

GO




-- Update

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_Update
GO

CREATE PROCEDURE [dbo].[uspContact_Update]
		@ContactID Int,
	@Name NVarChar(50),
	@Address NVarChar(50)

AS

SET NOCOUNT ON

UPDATE [dbo].[Contact] SET
		[Name] = @Name,
	[Address] = @Address
WHERE
		[ContactID] = @ContactID

GO




-- Delete
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspContact_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspContact_Delete
GO

CREATE PROCEDURE [dbo].[uspContact_Delete]
		@ContactID Int

AS

SET NOCOUNT ON

DELETE FROM [dbo].[Contact]
WHERE
		[ContactID] = @ContactID

GO




--  Member

-- GetByPK
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMember_GetByMemberID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMember_GetByMemberID
GO

CREATE PROCEDURE [dbo].[uspMember_GetByMemberID]
		@MemberID Int
AS

SET NOCOUNT ON

SELECT
		[MemberID],
	[Name]
FROM
	[dbo].[Member]
WHERE
		[MemberID] = @MemberID

GO




-- Insert
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMember_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMember_Insert
GO

CREATE PROCEDURE [dbo].[uspMember_Insert]
		@Name NVarChar(50)
	,
		@MemberID Int OUTPUT

AS

SET NOCOUNT ON

INSERT INTO [dbo].[Member] (
		[Name]
) VALUES (
		@Name
)

SET @MemberID = SCOPE_IDENTITY()

GO




-- Update

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMember_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMember_Update
GO

CREATE PROCEDURE [dbo].[uspMember_Update]
		@MemberID Int,
	@Name NVarChar(50)

AS

SET NOCOUNT ON

UPDATE [dbo].[Member] SET
		[Name] = @Name
WHERE
		[MemberID] = @MemberID

GO




-- Delete
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMember_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMember_Delete
GO

CREATE PROCEDURE [dbo].[uspMember_Delete]
		@MemberID Int

AS

SET NOCOUNT ON

DELETE FROM [dbo].[Member]
WHERE
		[MemberID] = @MemberID

GO




--  Memo

-- GetByPK
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMemo_GetByMemoID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMemo_GetByMemoID
GO

CREATE PROCEDURE [dbo].[uspMemo_GetByMemoID]
		@MemoID Int
AS

SET NOCOUNT ON

SELECT
		[MemoID],
	[WrittenAt],
	[Subject],
	[Body],
	[ReadCount],
	[ParentID]
FROM
	[dbo].[Memo]
WHERE
		[MemoID] = @MemoID

GO




-- Insert
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMemo_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMemo_Insert
GO

CREATE PROCEDURE [dbo].[uspMemo_Insert]
		@WrittenAt SmallDateTime,
	@Subject NVarChar(50),
	@Body NVarChar(MAX),
	@ReadCount Int,
	@ParentID Int
	,
		@MemoID Int OUTPUT

AS

SET NOCOUNT ON

INSERT INTO [dbo].[Memo] (
		[WrittenAt],
	[Subject],
	[Body],
	[ReadCount],
	[ParentID]
) VALUES (
		@WrittenAt,
	@Subject,
	@Body,
	@ReadCount,
	@ParentID
)

SET @MemoID = SCOPE_IDENTITY()

GO




-- Update

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMemo_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMemo_Update
GO

CREATE PROCEDURE [dbo].[uspMemo_Update]
		@MemoID Int,
	@WrittenAt SmallDateTime,
	@Subject NVarChar(50),
	@Body NVarChar(MAX),
	@ReadCount Int,
	@ParentID Int

AS

SET NOCOUNT ON

UPDATE [dbo].[Memo] SET
		[WrittenAt] = @WrittenAt,
	[Subject] = @Subject,
	[Body] = @Body,
	[ReadCount] = @ReadCount,
	[ParentID] = @ParentID
WHERE
		[MemoID] = @MemoID

GO




-- Delete
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspMemo_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspMemo_Delete
GO

CREATE PROCEDURE [dbo].[uspMemo_Delete]
		@MemoID Int

AS

SET NOCOUNT ON

DELETE FROM [dbo].[Memo]
WHERE
		[MemoID] = @MemoID

GO




--  Reading

-- GetByPK
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspReading_GetByMemberIDMemoID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspReading_GetByMemberIDMemoID
GO

CREATE PROCEDURE [dbo].[uspReading_GetByMemberIDMemoID]
		@MemberID Int,
	@MemoID Int
AS

SET NOCOUNT ON

SELECT
		[MemberID],
	[MemoID]
FROM
	[dbo].[Reading]
WHERE
		[MemberID] = @MemberID AND
	[MemoID] = @MemoID

GO




-- Insert
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspReading_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspReading_Insert
GO

CREATE PROCEDURE [dbo].[uspReading_Insert]
		@MemberID Int,
	@MemoID Int
	
	

AS

SET NOCOUNT ON

INSERT INTO [dbo].[Reading] (
		[MemberID],
	[MemoID]
) VALUES (
		@MemberID,
	@MemoID
)



GO




-- Update
/* not available for mapping table that has only two columns.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspReading_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspReading_Update
GO

CREATE PROCEDURE [dbo].[uspReading_Update]
		@MemberID Int,
	@MemoID Int

AS

SET NOCOUNT ON

UPDATE [dbo].[Reading] SET
	
WHERE
		[MemberID] = @MemberID AND
	[MemoID] = @MemoID

GO
not available for mapping table that has only two columns. */



-- Delete
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.uspReading_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.uspReading_Delete
GO

CREATE PROCEDURE [dbo].[uspReading_Delete]
		@MemberID Int,
	@MemoID Int

AS

SET NOCOUNT ON

DELETE FROM [dbo].[Reading]
WHERE
		[MemberID] = @MemberID AND
	[MemoID] = @MemoID

GO
